package com.apress.timesheets.dao.jdbc;

import java.sql.Connection;
import java.sql.Statement;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.apress.timesheets.dao.ConfigHelper;

public class JdbcConfigHelper implements ConfigHelper {

   private static final String DRIVER = "org.hsqldb.jdbcDriver";
   private static final String URL = "jdbc:hsqldb:mem:JdbcDaoTest";
   private static final String USERNAME = "sa";
   private static final String PASSWORD = "";
   
   private static final String[] DDL = new String[] {
      "drop schema public cascade",
      "create table Period (id bigint generated by default as identity (start with 1), endTime timestamp, note varchar(255), rate numeric, startTime timestamp, rateType_id varchar(255), primary key (id))",
      "create table RateType (id varchar(255) not null, primary key (id))",
      "create table Timesheet (id bigint generated by default as identity (start with 1), created timestamp, note varchar(255), startDate timestamp, useraccount bigint not null, primary key (id))",
      "create table Timesheet_Period (Timesheet_id bigint not null, periods_id bigint not null, unique (periods_id))",
      "create table UserAccount (id bigint generated by default as identity (start with 1), accountName varchar(255) not null, primary key (id), unique (accountName))",
      "create table UserRole (id bigint generated by default as identity (start with 1), roleName varchar(255) not null, primary key (id), unique (roleName))",
      "create table account_role (user bigint not null, role bigint not null, primary key (user, role))",
      "alter table Period add constraint FK_PERIOD_RATE_TYPE foreign key (rateType_id) references RateType",
      "alter table Timesheet add constraint FK_TIMESHEET_USER foreign key (useraccount) references UserAccount",
      "alter table Timesheet_Period add constraint FK_PT_TIMESHEET foreign key (Timesheet_id) references Timesheet",
      "alter table Timesheet_Period add constraint FK_PT_PERIOD foreign key (periods_id) references Period",
      "alter table account_role add constraint FK_AR_ROLE foreign key (role) references UserRole",
      "alter table account_role add constraint FK_AR_ACCOUNT foreign key (user) references UserAccount"      
   };
   
   private JdbcTemplate template;
   
   public void setUp() throws Exception {
      final DataSource ds = 
         new DriverManagerDataSource(DRIVER,URL,USERNAME,PASSWORD);
      template = new JdbcTemplate(ds);
      buildDatabase(ds);
   }
   
   private void buildDatabase(final DataSource ds) throws Exception {
      final Connection c = ds.getConnection();
      try {
         final Statement tableCreation = c.createStatement();
         for(final String ddl : DDL) {
            tableCreation.execute(ddl);
         }
      } finally {
         c.close();
      }
   }

   public void clear() {
      // No cacheing involved
   }
   
   public JdbcTemplate getTemplate() {
      return template;
   }

   public void setTemplate(JdbcTemplate template) {
      this.template = template;
   }
}
